package exttable.timetable.xlsgenerator;

import dbutils.DbUtils;
import exttable.SGTTestParametrs;
import exttable.formgenerator.StaticMethods;
import exttable.view.ViewTestComboBox;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import utils.Localisation;

import javax.swing.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.*;
import java.util.*;

/**
 * Created by IntelliJ IDEA.
 * User: Admin
 * Date: May 11, 2010
 * Time: 5:23:59 PM
 * To change this template use File | Settings | File Templates.
 */
public class StatReportAnalysis {
    public static final short FONT_HEIGHT = 12;
    public final static Map<String, Integer> mapDataTypes = new HashMap<String, Integer>();
    private static Map<String, String> referencedColumns = new HashMap<String, String>();
    private static Map<List<String>, String> dicValues = new HashMap<List<String>, String>();


    public static void formXLSReport(ViewTestComboBox vtc, String path, String phaseID, boolean newVersion, boolean translateColumnName) throws IOException {
        FileOutputStream out = null;
        Cell cell;
        Row row;

        String analysisName = vtc.getAnalysName();
        try {
            fillDicValue(analysisName);
        } catch (Exception e) {
            e.printStackTrace();
        }

        try {
            out = new FileOutputStream(path + ".xls");
            Workbook wb = new HSSFWorkbook();
            Sheet s = wb.createSheet();
            int rowNum = 1;
            CellStyle cs = wb.createCellStyle();
            Font f = wb.createFont();

            Connection c = DbUtils.getDBConnectionGeneral();
            PreparedStatement ps = c.prepareStatement(queryBuilder(analysisName));
            ps.setString(1, phaseID);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData rsm = rs.getMetaData();

            row = s.createRow(0);
            int cellPosition = 0;
            fillReferencedColumns(analysisName);
            for (int i = 1; i < rsm.getColumnCount(); i++) {

                cell = row.createCell(cellPosition);
                if (translateColumnName) {
                    cell.setCellValue(StaticMethods.returnTranslateField(analysisName, rsm.getColumnName(i)));
                } else {
                    cell.setCellValue(rsm.getColumnName(i));
                }

                if (referencedColumns.containsKey(rsm.getColumnName(i)) & newVersion) {
                    cellPosition += 1;
                    cell = row.createCell(cellPosition);
                    cell.setCellValue("description");

                }
                cellPosition += 1;
            }
            cellPosition = 0;
            while (rs.next()) {
                row = s.createRow(rowNum);
                for (int i = 1; i < rsm.getColumnCount(); i++) {

                    cell = row.createCell(cellPosition);
                    cell.setCellType(mapDataTypes.get(rsm.getColumnTypeName(i)));
                    if (mapDataTypes.get(rsm.getColumnTypeName(i)) == Cell.CELL_TYPE_NUMERIC) {
                        cell.setCellValue(rs.getDouble(i));
                        if (referencedColumns.containsKey(rsm.getColumnName(i)) & newVersion) {
                            cellPosition += 1;
                            cell = row.createCell(cellPosition);
                            List<String> key = new ArrayList<String>(3);
                            key.add(rsm.getColumnName(i));
                            key.add(referencedColumns.get(rsm.getColumnName(i)));
                            key.add(rs.getString(i));
                            cell.setCellValue(dicValues.get(key));


                        }

                    } else {
                        cell.setCellValue(rs.getString(i));
                    }
                    cellPosition += 1;
                }
                rowNum++;
                cellPosition = 0;
            }
            f.setFontHeightInPoints(FONT_HEIGHT);
            f.setBoldweight(Font.BOLDWEIGHT_BOLD);
            cs.setFont(f);

            wb.write(out);
            if (StaticMethods.getFrameByName("creatingExcellForAnalysys") != null) {
                StaticMethods.getFrameByName("creatingExcellForAnalysys").dispose();
            }
            out.close();
            JOptionPane.showMessageDialog(null, Localisation.getMessage("info.falewascreated"), Localisation.getMessage("info.title"), JOptionPane.INFORMATION_MESSAGE);
            c.close();
        } catch (IOException e) {
            JOptionPane.showMessageDialog(null, Localisation.getMessage("error.datawrite"), Localisation.getMessage("error.title"), JOptionPane.ERROR_MESSAGE);
            e.printStackTrace();
            throw new IOException("error");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            out.close();
        }
    }

    private static void fillReferencedColumns(String analysisName) throws Exception {
        Connection c = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = c.prepareStatement("SELECT distinct table_field, dic_type FROM dic_general where table_name = ?");
        ps.setString(1, analysisName);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            referencedColumns.put(rs.getString("table_field"), rs.getString("dic_type"));
        }

    }

    public static String queryBuilder(String anlysName) throws Exception {
        String query = "select p.probationer_code " +
                "   , p.group_id " +
                "   , v.sex_id " +
                "   , v.volunteer_date_birth " +
                "   , v.nationality_id " +
                "   , gsv.groups_schema_visits_id" +
                "   , dv.vizit_id " +
                "   , svt.time_mls" +
                "   , (svt.time_mls / 3600) as hours" +
                "   , (svt.time_mls / 86400) as days" +
                "   , t12.* " +
                "from" +
                "   " + anlysName + " t12 " +
                "   , visit_details vd " +
                "   , probationers p " +
                "   , groups_schema_visits gsv " +
                "   , timetable tt " +
                "   , dic_visits dv " +
                "   , schema_visit_time svt " +
                "   , groups g " +
                "   , volunteers v" +
                " where 1 = 1 " +
                "   and t12.test_id = vd.test_id" +
                "   and vd.probationer_id = p.probationer_id " +
                "   and vd.groups_schema_visits_id = gsv.groups_schema_visits_id" +
                "   and tt.groups_schema_visits_id = gsv.groups_schema_visits_id " +
                "   and vd.visit_id = tt.visit_id " +
                "   and tt.analysis_version_key in (" + getAnalysisVersion(anlysName) + ") " +
                "   and tt.visit_id = dv.vizit_id " +
                "   and dv.vizit_id = svt.visit_id " +
                "   and gsv.schema_visits_id = svt.schema_visits_id " +
                "   and p.group_id = g.group_id " +
                "   and g.phase_id = ? " +
                "   and v.volunteer_id = p.volunteer_id" +
                " order by " +
                " p.group_id, " +
                " svt.time_mls, " +
                " p.probationer_code";
        return query;
    }

    private static String getAnalysisVersion(String analysisName) throws Exception {
        String toReturn = "";
        Connection c = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = c.prepareStatement("select distinct(tpu.analysis_version_key) from dic_analysis_instr_invest_list dail   " +
                " inner join test_parametrs_units tpu on dail.analysis_instr_invest_list_id = tpu.analysis_instr_invest_list_id     " +
                " where dail.analysis_name = ?");
        ps.setString(1, analysisName);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            toReturn = toReturn + "'" + rs.getString(1) + "', ";
        }
        toReturn = toReturn.substring(0, toReturn.length() - 2);
        ps.close();
        return toReturn;
    }

    private static String getDicValue(int id, String tableName, String keyColumn, String analysisName) throws Exception {
        String toReturn;
        Connection c = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = c.prepareStatement("SELECT name_ru FROM dic_general where  1 = 1 " +
                "    and table_name = ? " +
                "    and table_field = ? " +
                "    and dic_type =  ? " +
                "    and dic_id = ?");
        ps.setString(1, analysisName);
        ps.setString(2, keyColumn);
        ps.setString(3, tableName);
        ps.setInt(4, id);
        ResultSet rs = ps.executeQuery();
        rs.next();
        toReturn = rs.getString(1);
        ps.close();
        c.close();
        return toReturn;
    }

    private static void fillDicValue(String analysisName) throws Exception {
        Connection c = DbUtils.getDBConnectionGeneral();
        PreparedStatement ps = c.prepareStatement("SELECT table_field, dic_type, dic_id, name_ru, name_en, name_kk  FROM dic_general where  1 = 1 " +
                "    and table_name = ?");
        ps.setString(1, analysisName);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            List<String> key = new ArrayList<String>(3);
            key.add(rs.getString(1));
            key.add(rs.getString(2));
            key.add(rs.getString(3));
            dicValues.put(key, rs.getString(SGTTestParametrs.getInstance().getCurrentLocal().getColumn()));
        }

        ps.close();
        c.close();
    }

    static {
        mapDataTypes.put("VARCHAR", Cell.CELL_TYPE_STRING);
        mapDataTypes.put("MEDIUMINT", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("INTEGER", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("TINYINT UNSIGNED", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("TINYINT", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("DECIMAL", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("SMALLINT", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("SMALLINT UNSIGNED", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("BIGINT UNSIGNED", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("DOUBLE", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("VARCHAR", Cell.CELL_TYPE_STRING);
        mapDataTypes.put("DATETIME", Cell.CELL_TYPE_STRING);
        mapDataTypes.put("DATETIME", Cell.CELL_TYPE_STRING);
        mapDataTypes.put("BIT", Cell.CELL_TYPE_NUMERIC);
        mapDataTypes.put("DATE", Cell.CELL_TYPE_STRING);

    }

}
